﻿/* Copy Write: AulacSoft */
/* Author write: Thangnc */
/* Code date: 12/17/2010 */

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Aulac.Rsc.Ems.Tags;
using Aulac.Srv.Db.Common;
using System.Xml;
using Aulac.Global.Xml;
using Aulac.Rsc.Ems;
using Aulac.Srv.Db.Data.Ems.InvJrns;
using Aulac.Rsc;
using System.Data;
using Aulac.Srv.Db.Data.Ems.AcntJrns;
using Aulac.Global.Common;
using Aulac.Global.Exceptions;

namespace Aulac.Srv.Db.Data.Ems.Stores.TranStores
{
    partial class VchTranStoreMng
    {
        #region Method Vch
        private string GetNewVchNoDb(string pVchType, ref string pVchName)
        {
            /* Code date: 12/17/2010 by Thangnc*/

            return GetNewVchNo(VchTranInfo.VchTranStoreHeader, tagVch.Inv.VchTranInvPrkID, pVchType, ref pVchName);
}

        private void AddVchHeader(XmlElement eVch, ref string pVchID)
        {
            /* Code date: 12/17/2010 by Thangnc*/

            sqlQuery = SqlHelper.CreateSqlInsert(TableNames.VchTranStoreHeader, VchTranInfo.ListH.GetRange(1, VchTranInfo.ListH.Count - 1));
            XmlElement eHeader = XmlProcess.GetVchHeaderElm(eVch);
            DbDataProvider.AddParamToCmd(mDbCmd, eHeader, VchTranInfo.ListH.GetRange(1, VchTranInfo.ListH.Count - 1).ToArray());

            if (DbDataProvider.ExecuteNonQuery(ref mDbCmd, sqlQuery) > 0)
                pVchID = DbDataProvider.SelectMaxID(mDbCmd, TableNames.VchTranStoreHeader, tagVch.Inv.VchTranInvPrkID);
            XmlProcess.SetAtrb(ref eHeader, tagVch.Inv.VchTranInvPrkID, pVchID);

        }
        
        private void UpdateVchHeader(XmlElement eVch)
        {
            /* Code date: 12/17/2010 by Thangnc*/

            XmlElement eHeader = XmlProcess.GetVchHeaderElm(eVch);
            string sVoucherID = XmlProcess.GetValueAtrb(eHeader, tagVch.Inv.VchTranInvPrkID);

            sqlQuery = SqlHelper.CreateSqlUpdate(TableNames.VchTranStoreHeader, VchTranInfo.ListH.GetRange(1, VchTranInfo.ListH.Count - 1).ToArray());
            sqlQuery += string.Format(" WHERE {0} = ? ", tagVch.Inv.VchTranInvPrkID);

            DbDataProvider.AddParamToCmd(mDbCmd, eHeader, VchTranInfo.ListH.GetRange(1, VchTranInfo.ListH.Count - 1).ToArray());
            DbDataProvider.AddParamToCmd(mDbCmd, false, sVoucherID);
            DbDataProvider.ExecuteNonQuery(ref mDbCmd, sqlQuery);

        }
       
        private void AddVchLines(XmlNodeList pList, string pVchID)
        {
            /* Code date: 12/17/2010 by Thangnc*/

            sqlQuery = SqlHelper.CreateSqlInsert(VchTranInfo.VchTranStoreLine, VchTranInfo.ListL.ToArray());
            for (int i = 0; i < pList.Count; i++)
            {
                XmlElement eLine = pList[i] as XmlElement;
                XmlProcess.SetAtrb(ref eLine, tagVch.Inv.VchTranInvPrkID, pVchID);
                DbDataProvider.AddParamToCmd(mDbCmd, eLine, VchTranInfo.ListL.ToArray());
                DbDataProvider.ExecuteNonQuery(ref mDbCmd, sqlQuery);
            }
        }

        private bool  SetInvJrn(XmlDataDocument xmlDoc, string pVchID)
        {
            XmlDataDocument xmlInvJrn = CreateXmlInvJrn(xmlDoc,  pVchID);
            InvJrnMng invJrn = new InvJrnMng(DbDataProvider.DbAulacConnection);
            XmlDataDocument xmlLostQty = null;
            if (invJrn.SetInvJrn(xmlInvJrn, out xmlLostQty) == false && xmlLostQty != null)
                throw new AulacException(Aulac.ErrCodes.ErrorCode.Error0010, xmlLostQty);
            return true;
        }

        private bool DelVchHeaderDb(string pVchID)
        {
            sqlQuery = SqlHelper.CreateSqlDelete(TableNames.VchTranStoreHeader, tagVch.Inv.VchTranInvPrkID);
            return DbDataProvider.ExecuteNonQuery(ref mDbCmd, sqlQuery, pVchID) >= 0;
        }
        private void DelVchLineDb(string pVchID)
        {
            /* Code date: 12/17/2010 by Thangnc*/

            sqlQuery = SqlHelper.CreateSqlDelete(VchTranInfo.VchTranStoreLine, tagVch.Inv.VchTranInvPrkID);
            DbDataProvider.ExecuteNonQuery(ref mDbCmd, sqlQuery, pVchID);
        }
        private void DelVchTranStore(string pVchType, string pVchID)
        {
            DelVchHeaderDb(pVchID);
            DelVchLineDb(pVchID);
        }
        public XmlDataDocument GetVoucher(string pVchTranInvPrkID, string pVchTypeID)
        {
            /* Code date: 12/17/2010 by Thangnc*/

            string strTran = Aulac.Global.Common.StringHelper.ConvertArrayToString(", h.", VchTranInfo.ListH.ToArray());

            sqlQuery = string.Format("SELECT h.{0} FROM {1} h ", strTran, TableNames.VchTranStoreHeader);
            sqlQuery += string.Format(" WHERE (h.{0} =  ?)", tagVch.Inv.VchTranInvPrkID);

            DbDataReader = DbDataProvider.ExecuteReader(mDbCmd, sqlQuery, pVchTranInvPrkID);
            string sStoreFrom = "", sStoreTo = "";
            XmlDataDocument xmlDoc = CreateXmlDocument("", "");
            if (DbDataReader.Read())
            {
                XmlElement eHeader = XmlProcess.AddVchHeaderElm(ref xmlDoc);
                DbDataProvider.GetValuesToXml(DbDataReader, ref eHeader, VchTranInfo.ListH.ToArray());

                sStoreFrom = DbDataProvider.GetValue(DbDataReader, tagVch.Inv.StoreRelsPrkID);
                sStoreTo = DbDataProvider.GetValue(DbDataReader, tagVch.Inv.StoreRecPrkID);
            }
            DbDataReader.Close();
            string[] arrV = new string[] { tagVch.Inv.ItemPrkID, tagVch.Inv.Quantity, tagVch.Inv.AmtLineInExc, tagVch.Inv.AmtTaxLineInExc, tagVch.Inv.AmtTtlLineInExc, tagVch.Inv.PrimeCostInExc };
            string[] arrItem = new string[] { tagVch.Inv.ItemID, tagVch.Inv.ItemName, tagVch.Inv.Unit };

            string str = string.Format("SELECT TOP 1 {0} FROM {1} ", tagVch.Inv.VchRecInvPrkID, TableNames.VchRecInvHeader);
            str += string.Format(" WHERE ({0} = ?) AND ({1} = ?)", tagVch.Inv.VchOrgID, tagVch.Inv.VchTypeID);

            sqlQuery = string.Format(" SELECT l.{0}, i.{1} FROM {2} l ", Aulac.Global.Common.StringHelper.ConvertArrayToString(", l.", arrV), Aulac.Global.Common.StringHelper.ConvertArrayToString(", i.", arrItem), TableNames.VchRecInvLine);
            sqlQuery += string.Format(" INNER JOIN {0} i ON l.{1} = i.{2}", TableNames.Dm_MdnItems, tagVch.Inv.ItemPrkID, tagVch.Inv.ItemPrkID);
            sqlQuery += string.Format(" WHERE l.{0} IN ({1})", tagVch.Inv.VchRecInvPrkID, str);

            DbDataReader = DbDataProvider.ExecuteReader(mDbCmd, sqlQuery, pVchTranInvPrkID, pVchTypeID);
            XmlElement eLines = XmlProcess.AddVchLinesElm(ref xmlDoc);
            while (DbDataReader.Read())
            {
                XmlElement eline = XmlProcess.AddVchLineItemElm(eLines);
                DbDataProvider.GetValuesToXml(DbDataReader, ref eline, arrV);
                DbDataProvider.GetValuesToXml(DbDataReader, ref eline, arrItem);
            }
            return xmlDoc;

        }
        private DataTable GetVchTable(string pVchTranInvPrkID)
        {
            sqlQuery = string.Format("SELECT {0}, {1}, {2}, {3}", tagVch.Inv.ItemLine, tagVch.Inv.ItemPrkID, tagVch.Inv.StoreRelsPrkID, tagVch.Inv.Quantity);
            sqlQuery += string.Format(" FROM {0} ", TableNames.VchTranStoreLine);
            sqlQuery += string.Format(" WHERE {0} = ?", tagVch.Inv.VchTranInvPrkID);
            return DbDataProvider.ExecuteReaderDataToTable(mDbCmd, sqlQuery, pVchTranInvPrkID);
        }
        private DataTable GetListVchTranStroreDb(XmlDataDocument xmlParam)
        {
            //DateTime dtFrom = XmlProcess.GetValueParamDate(xmlParam, tagVch.Rpt.VchDateFrom, DbDataProvider.CultureInfo);
            //DateTime dtTo = XmlProcess.GetValueParamDate(xmlParam, tagVch.Rpt.VchDateTo, DbDataProvider.CultureInfo);
            //List<string> lst = VchTranInfo.ListH.ToList();
            //sqlQuery = string.Format("SELECT h.{0}, sum(l.{1}) as {2}", StringHelper.ConvertArrayToString(" , h.", lst), tagVch.Inv.AmtPrimeCostLineInExc, tagVch.Inv.AmtTtlPrimeCostInExc);
            //sqlQuery += string.Format(" FROM {0} h INNER JOIN {1} l", VchTranInfo.VchTranStoreHeader, VchTranInfo.VchTranStoreLine);
            //sqlQuery += string.Format(" ON h.{0} = l.{0}", tagVch.Inv.VchTranInvPrkID);




            //sqlQuery += string.Format(" WHERE (h.{0} BETWEEN ? AND ?)", tagVch.Inv.VchDate);
            //sqlQuery += string.Format(" GROUP BY h.{0}", StringHelper.ConvertArrayToString(", h.", lst));
            //sqlQuery += string.Format(" ORDER BY h.{0}, h.{1}", tagVch.Inv.VchDate, tagVch.Inv.VchNo);
            //return DbDataProvider.ExecuteReaderDataToTable(mDbCmd, sqlQuery, dtFrom, dtTo);


            //New
            DateTime dtFrom = XmlProcess.GetValueParamDate(xmlParam, tagVch.Rpt.VchDateFrom, DbDataProvider.CultureInfo);
            DateTime dtTo = XmlProcess.GetValueParamDate(xmlParam, tagVch.Rpt.VchDateTo, DbDataProvider.CultureInfo);
            List<string> lst = VchTranInfo.ListH.ToList();

            string[] arrTH = new string[] { 
                tagVch.Inv.VchNo, 
                tagVch.Inv.VchDate, 
                tagVch.Inv.VchName, 
                tagVch.Inv.StoreRecPrkID, 
                tagVch.Inv.PsnInStockRecPrkID,
                tagVch.Inv.StoreRelsPrkID, 
                tagVch.Inv.PsnInStockRelPrkID, 
                tagVch.Inv.Note};
            
            sqlQuery = string.Format(@"SELECT DISTINCT Sh.{0}, ", StringHelper.ConvertArrayToString(", Sh.", lst));// arrTH));
            sqlQuery += string.Format(" PRels.{0} AS {1}, ", tagItems.Psn.PsnName, tagVch.Inv.PsnInStockRelName);
            sqlQuery += string.Format(" Prec.{0} AS {1}, ", tagItems.Psn.PsnName, tagVch.Inv.PsnInStockRecName);
            sqlQuery += string.Format(" SRels.{0} AS {1}, ", tagItems.Store.StoreName, tagVch.Inv.StoreRelsName);
            sqlQuery += string.Format(" SRec.{0} AS {1} ", tagItems.Store.StoreName, tagVch.Inv.StoreRecName);

            sqlQuery += string.Format(" FROM {0} AS Sh LEFT OUTER JOIN {1} AS SRels", TableNames.VchTranStoreHeader, TableNames.Dm_MdnStores);
            sqlQuery += string.Format(" ON Sh.{0} = SRels.{1}", tagVch.Inv.StoreRelsPrkID, tagItems.Store.StorePrkID);
            sqlQuery += string.Format(" LEFT OUTER JOIN {0} AS PRels ON Sh.{1} = PRels.{2}", TableNames.Dm_Personnels, tagVch.Inv.PsnInStockRelPrkID, tagItems.Psn.PsnPrkID);
            sqlQuery += string.Format(" LEFT OUTER JOIN {0} AS PRec ON Sh.{1} = PRec.{2}", TableNames.Dm_Personnels, tagVch.Inv.PsnInStockRecPrkID, tagItems.Psn.PsnPrkID);
            sqlQuery += string.Format(" LEFT OUTER JOIN {0} AS SRec ON Sh.{1} = SRec.{2}",TableNames.Dm_MdnStores, tagVch.Inv.StoreRecPrkID, tagItems.Store.StorePrkID);

            sqlQuery += string.Format(" WHERE (Sh.{0} BETWEEN ? AND ?)", tagVch.Inv.VchDate);
            //sqlQuery += string.Format(" GROUP BY Sh.{0}", StringHelper.ConvertArrayToString(", Sh.", lst));
            sqlQuery += string.Format(" ORDER BY Sh.{0}, Sh.{1}", tagVch.Inv.VchDate, tagVch.Inv.VchNo);
            return DbDataProvider.ExecuteReaderDataToTable(mDbCmd, sqlQuery, dtFrom, dtTo);
        }
        private XmlDataDocument GetVchTranStoreDb(XmlDataDocument xmlParam)
        {
            /* Author Write: thang */
            /* Code Update: 01/04/2011 by H.Rin */

            string sVchTranID = XmlProcess.GetValueParam(xmlParam, tagVch.Inv.VchTranInvPrkID);
            sqlQuery = string.Format("SELECT h.{0} FROM {1} h", StringHelper.ConvertArrayToString(", h.", VchTranInfo.ListH), VchTranInfo.VchTranStoreHeader);
            sqlQuery += string.Format(" WHERE {0} = ?", tagVch.Inv.VchTranInvPrkID);
            DataTable tb = DbDataProvider.ExecuteReaderDataToTable(mDbCmd, sqlQuery, sVchTranID);
            if (ValueHelper.isEmpty(tb))
                return null;
            XmlDataDocument outDoc  = CreateXmlDocument("", "");
            XmlElement eHeader = XmlProcess.AddVchHeaderElm(ref outDoc);
            List<string> arrI = new List<string>{ tagItems.Item.ItemPrkID, tagItems.Item.ItemID, tagItems.Item.ItemName, tagItems.Item.ItemGrpID, tagItems.Item.Unit, tagItems.Item.IsItemInStock, tagItems.Item.SvcInsTypeID, tagItems.Item.ItemFncStylePrkID };
            DbDataProvider.GetValuesToXml(tb.Rows[0], ref eHeader, VchTranInfo.ListH.ToArray());
            sqlQuery = string.Format("SELECT {0}, I.{1} FROM {2} AS T LEFT OUTER JOIN {3} AS I ", StringHelper.ConvertArrayToString(", T.", VchTranInfo.ListL), StringHelper.ConvertArrayToString(", I.", arrI), VchTranInfo.VchTranStoreLine, TableNames.Dm_MdnItems);
            sqlQuery += string.Format(" ON T.{0} = I.{0} ", tagVch.Inv.ItemPrkID);
            sqlQuery += string.Format(" WHERE {0} = ?", tagVch.Inv.VchTranInvPrkID);
            tb = DbDataProvider.ExecuteReaderDataToTable(mDbCmd, sqlQuery, sVchTranID);
            XmlElement eLines = XmlProcess.AddVchLinesElm(ref outDoc);
            arrI.AddRange(VchTranInfo.ListL);
            DbDataProvider.GetValuesToChildXml(tb, ref eLines,arrI.ToArray());
            return outDoc;
        }
        #endregion
        #region IVchInStock Members
        private XmlDataDocument CreateXmlInvJrn(XmlDataDocument xmlDoc, string pVchTranInvPrkID)
        {
            /* Code date: 12/17/2010 by Thangnc*/

            XmlDataDocument xmlStore = CreateXmlDocument("", "");
            XmlElement eInvJrn = XmlInvJrnHelper.AddInvJrnElm(xmlStore);
            XmlElement eHeaderTmp = XmlProcess.GetVchHeaderElm(xmlDoc);
            XmlElement eInvH = XmlInvJrnHelper.AddInvJrnHeader(xmlStore);
            XmlProcess.SetAtrb(ref eInvH, tagVch.Inv.VchOrgID, pVchTranInvPrkID);
            XmlProcess.SetAtrb(ref eInvH, tagVch.Inv.VchOrgTypeID, XmlProcess.GetValueAtrb(eHeaderTmp, tagVch.Inv.VchTypeID));
            XmlProcess.SetAtrb(ref eInvH, tagVch.Inv.VchDate, XmlProcess.GetValueAtrb(eHeaderTmp, tagVch.Inv.VchDate));
            XmlProcess.SetAtrb(ref eInvH, tagVch.Inv.VchNo, XmlProcess.GetValueAtrb(eHeaderTmp, tagVch.Inv.VchNo));
            XmlProcess.SetAtrb(ref eInvH, tagVch.Inv.VchName, XmlProcess.GetValueAtrb(eHeaderTmp, tagVch.Inv.VchName));

            XmlProcess.SetAtrb(ref eInvH, tagVch.Inv.Note, XmlProcess.GetValueAtrb(eHeaderTmp, tagVch.Inv.Note));
            XmlProcess.SetAtrb(ref eInvH, tagVch.Inv.PsnInStockPrkID, XmlProcess.GetValueAtrb(eHeaderTmp, tagVch.Inv.PsnInStockPrkID));
            //XmlProcess.SetAtrb(ref eInvH, tagVch.Inv.TagStore, pTagStores);
            string sStoreRecPrkID = XmlProcess.GetValueAtrb(eHeaderTmp, tagVch.Inv.StoreRecPrkID);
            string sStoreRelsPrkID = XmlProcess.GetValueAtrb(eHeaderTmp, tagVch.Inv.StoreRelsPrkID);

            XmlElement eInvJrnLines = XmlInvJrnHelper.AddInvJrnLines(eInvJrn);
            XmlNodeList list = XmlProcess.GetListVchLineItem(xmlDoc);
            int index = 0;
            foreach (XmlElement el in list)
            {
                XmlElement eLine = XmlProcess.ImportNode(ref eInvJrnLines, el);

                XmlProcess.SetAtrb(ref eLine, tagVch.Inv.Quantity, -XmlProcess.GetValueDoubleAtrb(el, tagVch.Inv.Quantity));
                XmlProcess.SetAtrb(ref eLine, tagVch.Inv.StorePrkID, sStoreRelsPrkID);
                XmlProcess.SetAtrb(ref eLine, tagVch.Inv.TagStore, TagStores.OutputStore);
                XmlProcess.SetAtrb(ref eLine, tagVch.Inv.ItemLine, ++index);

                eLine = XmlProcess.ImportNode(ref eInvJrnLines, el);
                XmlProcess.SetAtrb(ref eLine, tagVch.Inv.StorePrkID, sStoreRecPrkID);
                XmlProcess.SetAtrb(ref eLine, tagVch.Inv.TagStore, TagStores.InputStore);
                XmlProcess.SetAtrb(ref eLine, tagVch.Inv.ItemLine, ++index);

                /*XmlProcess.SetAtrb(ref eLine, tagVch.Inv.TagStore, pTagStores);
                if (pTagStores == TagStores.OutputStore)
                {
                    XmlProcess.SetAtrb(ref eLine, tagVch.Inv.Quantity, -XmlProcess.GetValueDoubleAtrb(el, tagVch.Inv.Quantity));
                    XmlProcess.SetAtrb(ref eLine, tagVch.Inv.StorePrkID, XmlProcess.GetValueAtrb(el, tagVch.Inv.StoreRelsPrkID));
                }
                else
                {
                    XmlProcess.SetAtrb(ref eLine, tagVch.Inv.StorePrkID, XmlProcess.GetValueAtrb(el, tagVch.Inv.StoreRecPrkID));
                }
                 */

            }
            return xmlStore;
        }

        public void ReUpdateAcntJrn(IAcntJrnMng pAcntJrn, string pOrgVchID, string pVchTypeID)
        {
            /* Please Code */
            throw new NotImplementedException();
        }

        #endregion
    }
}
